Malloy
About Malloy
Scatter Charts
Shape Maps
The plugin currently supports US maps and state names. The model and data styles for the subsequent examples are:
source: airports is table('malloy-data.faa.airports') { primary_key: code measure: airport_count is count() query: by_state is { where: state != null group_by: state aggregate: airport_count } }
Data Styles
{ "by_state": { "renderer": "shape_map" } }
Run as a simple query
query: airports -> { nest: by_state }| by_​state |
|---|
[ { "by_state": [ { "state": "TX", "airport_count": 1845 }, { "state": "CA", "airport_count": 984 }, { "state": "IL", "airport_count": 890 }, { "state": "FL", "airport_count": 856 }, { "state": "PA", "airport_count": 804 }, { "state": "OH", "airport_count": 749 }, { "state": "IN", "airport_count": 643 }, { "state": "AK", "airport_count": 608 }, { "state": "NY", "airport_count": 576 }, { "state": "WI", "airport_count": 543 }, { "state": "MO", "airport_count": 537 }, { "state": "MN", "airport_count": 507 }, { "state": "LA", "airport_count": 500 }, { "state": "MI", "airport_count": 489 }, { "state": "WA", "airport_count": 484 }, { "state": "OK", "airport_count": 443 }, { "state": "OR", "airport_count": 441 }, { "state": "GA", "airport_count": 440 }, { "state": "ND", "airport_count": 436 }, { "state": "CO", "airport_count": 425 }, { "state": "VA", "airport_count": 421 }, { "state": "KS", "airport_count": 415 }, { "state": "NC", "airport_count": 400 }, { "state": "NJ", "airport_count": 378 }, { "state": "AZ", "airport_count": 319 }, { "state": "IA", "airport_count": 319 }, { "state": "NE", "airport_count": 308 }, { "state": "AR", "airport_count": 299 }, { "state": "TN", "airport_count": 285 }, { "state": "AL", "airport_count": 260 }, { "state": "MT", "airport_count": 259 }, { "state": "MS", "airport_count": 243 }, { "state": "ID", "airport_count": 238 }, { "state": "MD", "airport_count": 229 }, { "state": "MA", "airport_count": 225 }, { "state": "KY", "airport_count": 202 }, { "state": "SC", "airport_count": 189 }, { "state": "NM", "airport_count": 181 }, { "state": "SD", "airport_count": 180 }, { "state": "ME", "airport_count": 164 }, { "state": "CT", "airport_count": 153 }, { "state": "UT", "airport_count": 140 }, { "state": "NV", "airport_count": 128 }, { "state": "WV", "airport_count": 116 }, { "state": "WY", "airport_count": 115 }, { "state": "NH", "airport_count": 112 }, { "state": "VT", "airport_count": 81 }, { "state": "HI", "airport_count": 52 }, { "state": "DE", "airport_count": 42 }, { "state": "PR", "airport_count": 40 }, { "state": "RI", "airport_count": 28 }, { "state": "DC", "airport_count": 20 }, { "state": "CQ", "airport_count": 11 }, { "state": "VI", "airport_count": 9 }, { "state": "AS", "airport_count": 4 }, { "state": "GU", "airport_count": 3 }, { "state": "MQ", "airport_count": 1 }, { "state": "WQ", "airport_count": 1 } ] } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set=1 THEN airports.state END as state__1, CASE WHEN group_set=1 THEN COUNT( 1) END as airport_count__1 FROM `malloy-data.faa.airports` as airports CROSS JOIN (SELECT row_number() OVER() -1 group_set FROM UNNEST(GENERATE_ARRAY(0,1,1))) WHERE (group_set NOT IN (1) OR (group_set IN (1) AND airports.state IS NOT NULL)) GROUP BY 1,2 ) SELECT ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT( state__1 as state, airport_count__1 as airport_count ) END IGNORE NULLS ORDER BY airport_count__1 desc ) as by_state FROM __stage0
Run as a trellis
By calling the configured map as a nested subtable, a trellis is formed.
query: airports -> { group_by: faa_region aggregate: airport_count nest: by_state }
| faa_​region | airport_​count | by_​state |
|---|---|---|
| AGL | 4,437 | |
| ASW | 3,268 | |
| ASO | 2,924 | |
| AEA | 2,586 | |
| ANM | 2,102 |
[ { "faa_region": "AGL", "airport_count": 4437, "by_state": [ { "state": "IL", "airport_count": 890 }, { "state": "OH", "airport_count": 749 }, { "state": "IN", "airport_count": 643 }, { "state": "WI", "airport_count": 543 }, { "state": "MN", "airport_count": 507 }, { "state": "MI", "airport_count": 489 }, { "state": "ND", "airport_count": 436 }, { "state": "SD", "airport_count": 180 } ] }, { "faa_region": "ASW", "airport_count": 3268, "by_state": [ { "state": "TX", "airport_count": 1845 }, { "state": "LA", "airport_count": 500 }, { "state": "OK", "airport_count": 443 }, { "state": "AR", "airport_count": 299 }, { "state": "NM", "airport_count": 181 } ] }, { "faa_region": "ASO", "airport_count": 2924, "by_state": [ { "state": "FL", "airport_count": 856 }, { "state": "GA", "airport_count": 440 }, { "state": "NC", "airport_count": 400 }, { "state": "TN", "airport_count": 285 }, { "state": "AL", "airport_count": 260 }, { "state": "MS", "airport_count": 243 }, { "state": "KY", "airport_count": 202 }, { "state": "SC", "airport_count": 189 }, { "state": "PR", "airport_count": 40 }, { "state": "VI", "airport_count": 9 } ] }, { "faa_region": "AEA", "airport_count": 2586, "by_state": [ { "state": "PA", "airport_count": 804 }, { "state": "NY", "airport_count": 576 }, { "state": "VA", "airport_count": 421 }, { "state": "NJ", "airport_count": 378 }, { "state": "MD", "airport_count": 229 }, { "state": "WV", "airport_count": 116 }, { "state": "DE", "airport_count": 42 }, { "state": "DC", "airport_count": 20 } ] }, { "faa_region": "ANM", "airport_count": 2102, "by_state": [ { "state": "WA", "airport_count": 484 }, { "state": "OR", "airport_count": 441 }, { "state": "CO", "airport_count": 425 }, { "state": "MT", "airport_count": 259 }, { "state": "ID", "airport_count": 238 }, { "state": "UT", "airport_count": 140 }, { "state": "WY", "airport_count": 115 } ] } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set IN (0,1) THEN airports.faa_region END as faa_region__0, CASE WHEN group_set=0 THEN COUNT( 1) END as airport_count__0, CASE WHEN group_set=1 THEN airports.state END as state__1, CASE WHEN group_set=1 THEN COUNT( 1) END as airport_count__1 FROM `malloy-data.faa.airports` as airports CROSS JOIN (SELECT row_number() OVER() -1 group_set FROM UNNEST(GENERATE_ARRAY(0,1,1))) WHERE (group_set NOT IN (1) OR (group_set IN (1) AND airports.state IS NOT NULL)) GROUP BY 1,2,4 ) SELECT faa_region__0 as faa_region, ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count, ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT( state__1 as state, airport_count__1 as airport_count ) END IGNORE NULLS ORDER BY airport_count__1 desc ) as by_state FROM __stage0 GROUP BY 1 ORDER BY 2 desc
Run as a trellis, repeated with different filters
query: airports -> { group_by: faa_region aggregate: airport_count nest: heliports is by_state { where: fac_type = 'HELIPORT' } seaplane_bases is by_state { where: fac_type = 'SEAPLANE BASE' } }
| faa_​region | airport_​count | heliports | seaplane_​bases |
|---|---|---|---|
| AGL | 4,437 | ||
| ASW | 3,268 | ||
| ASO | 2,924 | ||
| AEA | 2,586 | ||
| ANM | 2,102 |
[ { "faa_region": "AGL", "airport_count": 4437, "heliports": [ { "state": "IL", "airport_count": 245 }, { "state": "OH", "airport_count": 201 }, { "state": "IN", "airport_count": 115 }, { "state": "MI", "airport_count": 87 }, { "state": "WI", "airport_count": 85 }, { "state": "MN", "airport_count": 51 }, { "state": "SD", "airport_count": 26 }, { "state": "ND", "airport_count": 16 } ], "seaplane_bases": [ { "state": "MN", "airport_count": 72 }, { "state": "WI", "airport_count": 16 }, { "state": "IN", "airport_count": 12 }, { "state": "IL", "airport_count": 8 }, { "state": "MI", "airport_count": 7 }, { "state": "OH", "airport_count": 2 }, { "state": "SD", "airport_count": 1 }, { "state": "ND", "airport_count": 1 } ] }, { "faa_region": "ASW", "airport_count": 3268, "heliports": [ { "state": "TX", "airport_count": 435 }, { "state": "LA", "airport_count": 229 }, { "state": "OK", "airport_count": 92 }, { "state": "AR", "airport_count": 80 }, { "state": "NM", "airport_count": 25 } ], "seaplane_bases": [ { "state": "LA", "airport_count": 17 }, { "state": "OK", "airport_count": 1 }, { "state": "NM", "airport_count": 1 } ] }, { "faa_region": "ASO", "airport_count": 2924, "heliports": [ { "state": "FL", "airport_count": 280 }, { "state": "GA", "airport_count": 103 }, { "state": "TN", "airport_count": 87 }, { "state": "AL", "airport_count": 75 }, { "state": "NC", "airport_count": 70 }, { "state": "KY", "airport_count": 55 }, { "state": "MS", "airport_count": 49 }, { "state": "SC", "airport_count": 27 }, { "state": "PR", "airport_count": 20 }, { "state": "VI", "airport_count": 4 } ], "seaplane_bases": [ { "state": "FL", "airport_count": 43 }, { "state": "AL", "airport_count": 3 }, { "state": "VI", "airport_count": 3 }, { "state": "TN", "airport_count": 2 }, { "state": "SC", "airport_count": 2 }, { "state": "PR", "airport_count": 1 }, { "state": "NC", "airport_count": 1 }, { "state": "GA", "airport_count": 1 }, { "state": "KY", "airport_count": 1 } ] }, { "faa_region": "AEA", "airport_count": 2586, "heliports": [ { "state": "PA", "airport_count": 307 }, { "state": "NJ", "airport_count": 247 }, { "state": "NY", "airport_count": 156 }, { "state": "VA", "airport_count": 126 }, { "state": "MD", "airport_count": 64 }, { "state": "WV", "airport_count": 33 }, { "state": "DC", "airport_count": 18 }, { "state": "DE", "airport_count": 13 } ], "seaplane_bases": [ { "state": "NY", "airport_count": 23 }, { "state": "NJ", "airport_count": 10 }, { "state": "PA", "airport_count": 10 }, { "state": "WV", "airport_count": 10 }, { "state": "VA", "airport_count": 4 }, { "state": "MD", "airport_count": 3 }, { "state": "DE", "airport_count": 1 } ] }, { "faa_region": "ANM", "airport_count": 2102, "heliports": [ { "state": "CO", "airport_count": 165 }, { "state": "WA", "airport_count": 130 }, { "state": "OR", "airport_count": 100 }, { "state": "UT", "airport_count": 43 }, { "state": "ID", "airport_count": 36 }, { "state": "MT", "airport_count": 29 }, { "state": "WY", "airport_count": 24 } ], "seaplane_bases": [ { "state": "WA", "airport_count": 15 }, { "state": "ID", "airport_count": 5 }, { "state": "OR", "airport_count": 3 }, { "state": "MT", "airport_count": 2 } ] } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set IN (0,1,2) THEN airports.faa_region END as faa_region__0, CASE WHEN group_set=0 THEN COUNT( 1) END as airport_count__0, CASE WHEN group_set=1 THEN airports.state END as state__1, CASE WHEN group_set=1 THEN COUNT( 1) END as airport_count__1, CASE WHEN group_set=2 THEN airports.state END as state__2, CASE WHEN group_set=2 THEN COUNT( 1) END as airport_count__2 FROM `malloy-data.faa.airports` as airports CROSS JOIN (SELECT row_number() OVER() -1 group_set FROM UNNEST(GENERATE_ARRAY(0,2,1))) WHERE ((group_set NOT IN (1) OR (group_set IN (1) AND (airports.state IS NOT NULL) AND (airports.fac_type='HELIPORT')))) AND ((group_set NOT IN (2) OR (group_set IN (2) AND (airports.state IS NOT NULL) AND (airports.fac_type='SEAPLANE BASE')))) GROUP BY 1,2,4,6 ) SELECT faa_region__0 as faa_region, ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count, ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT( state__1 as state, airport_count__1 as airport_count ) END IGNORE NULLS ORDER BY airport_count__1 desc ) as heliports, ARRAY_AGG(CASE WHEN group_set=2 THEN STRUCT( state__2 as state, airport_count__2 as airport_count ) END IGNORE NULLS ORDER BY airport_count__2 desc ) as seaplane_bases FROM __stage0 GROUP BY 1 ORDER BY 2 desc